package com.pucong.gitee.database.backup.business.database.util;

import com.pucong.gitee.database.backup.business.database.mapper.AutoExeSqlLogMapper;
import com.pucong.gitee.database.backup.business.database.util.jdbcPool.JdbcConnectionManager;
import com.pucong.gitee.database.backup.common.entity.database.AutoDataSource;
import com.pucong.gitee.database.backup.common.entity.database.BackupExeSqlLog;
import com.pucong.gitee.database.backup.common.entity.database.vo.DatabaseTableFiledVo;
import com.pucong.gitee.database.backup.common.entity.database.vo.DatabaseTableVo;
import com.pucong.gitee.database.backup.common.entity.database.vo.ExeSqlResult;
import com.pucong.gitee.database.backup.common.selfenum.CommonBoolenEnum;
import com.pucong.gitee.database.backup.configuration.exception.BusinessException;
import com.pucong.gitee.database.backup.utils.UUID;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import java.util.*;

public abstract class AbstractOperationService implements OperationService {

    private static Logger log = LoggerFactory.getLogger(AbstractOperationService.class);

    protected static final String URL_PREFIX = "jdbc:postgresql://";

    protected static final String URL_LAST = "useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true";

    private CallableStatement callableStatement = null;
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rst = null;
    protected AutoDataSource autoDataSource = null;

    protected String driverClassName = null;
    protected String url = null;
    protected String username = null;
    protected String password = null;
    private String sessionId = null;
    private AutoExeSqlLogMapper autoExeSqlLogMapper = null;

    @Override
    public String getSessionId() {
        return sessionId;
    }

    @Override
    public void setSessionId(String sessionId) {
        this.sessionId = sessionId;
    }

    /**
     * 获取数据库名列表
     *
     * @return
     */
    public String getGET_BASE_LIST_SQL() {
        return "select datname dataBaseName " +
                " from pg_database t1 " +
                "where 1=1 " +
                "and datname not in ('template0','template1') " +
                "order by datname";
    }

    /**
     * 获取数据库下模式名列表
     *
     * @return
     */
    public String getGET_BASE_SCHEMA_LIST_SQL() {
        return "SELECT schema_name schemaName FROM information_schema.schemata where schema_name not in ('information_schema','pg_catalog','pg_toast_temp_1','pg_temp_1','pg_toast') and catalog_name=? ";
    }

    /**
     * 获取表字段列表
     *
     * @return
     */
    public String getGET_TABLE_LIST_SQL() {
        return "SELECT DISTINCT" +
                " a.schemaname," +
                " a.tablename tableName," +
                " b.reltuples as rowCounts," +
                " obj_description ( b.relfilenode, 'pg_class' ) AS tableComment " +
                "FROM" +
                " pg_tables A," +
                " pg_class b " +
                "WHERE A.tablename = b.relname" +
                " and A.schemaname=?";
    }

    /**
     * 获取表字段列表
     *
     * @return
     */
    public String getGET_TABLE_FILED_LIST_SQL() {
        return "SELECT a.attnum, " +
                "       a.attname AS filedName, " +
                "       t.typname AS filedType, " +
                "       a.attlen AS filedSize, " +
                "       a.atttypmod AS lengthvar, " +
                "       a.attnotnull AS notNull, " +
                "       b.description AS filedComment " +
                "  FROM pg_class c, " +
                "       pg_attribute a " +
                "       LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid, " +
                "       pg_type t,pg_namespace d  " +
                " WHERE 1=1 " +
                " and d.nspname=? " +
                " and  c.relname = ? " +
                " and c.relkind in ('r', 'v', 'm') " +
                " and d.nspname not in ('pg_catalog', 'pg_toast', 'information_schema') " +
                " and a.attnum > 0 " +
                " and c.relnamespace=d.oid " +
                " and a.attrelid = c.oid " +
                " and a.atttypid = t.oid " +
                " ORDER BY a.attnum ";
    }

    public AbstractOperationService(AutoDataSource autoDataSource, AutoExeSqlLogMapper autoExeSqlLogMapper) {
        this.autoDataSource = autoDataSource;
        this.username = autoDataSource.getUsername();
        this.password = autoDataSource.getPassword();
        this.autoExeSqlLogMapper = autoExeSqlLogMapper;
        initUrl();
    }


    @Override
    public void initUrl() {
        this.url = URL_PREFIX + autoDataSource.getHost() + ":" + autoDataSource.getPort() + "/" + autoDataSource.getDatabaseName() + "?currentSchema=" + autoDataSource.getSchemaName() + "&" + URL_LAST;
    }

    @Override
    public void setDataBase(String databaseName) {
        autoDataSource.setDatabaseName(databaseName);
        initUrl();
    }

    @Override
    public void setSchema(String schema) {
        autoDataSource.setSchemaName(schema);
        initUrl();
    }

    @Override
    public List<String> getDataBaseList() {
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_BASE_LIST_SQL(), null);
        final List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        List<String> strings = new ArrayList<>(resultList.size());
        for (Map<String, Object> stringObjectMap : resultList) {
            Collection<Object> values = stringObjectMap.values();
            for (Object value : values) {
                strings.add(value.toString());
            }
        }
        return strings;
    }

    @Override
    public List<String> getSchemaList(String databaseName) {
        List<String> resultStrList = new ArrayList<>();
        Object[] params = new Object[1];
        params[0] = databaseName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_BASE_SCHEMA_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            resultStrList.add(stringObjectMap.get("schemaname").toString());
        }
        return resultStrList;
    }

    @Override
    public List<DatabaseTableVo> getTableListByDBInfo(String databaseName, String schemaName) {
        List<DatabaseTableVo> resultTableList = new ArrayList<>();
        Object[] params = new Object[1];
        params[0] = schemaName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DatabaseTableVo databaseTableVo = new DatabaseTableVo();
            String tableName = (String) stringObjectMap.get("tablename");
            String count = stringObjectMap.get("rowcounts") + "";

            Object tableComment1 = stringObjectMap.get("tablecomment");
            String tableComment = tableComment1 == null ? null : tableComment1.toString();
            databaseTableVo.setDataSize(new BigDecimal(count));
            databaseTableVo.setTableName(tableName);
            databaseTableVo.setTableComment(tableComment);
            resultTableList.add(databaseTableVo);
        }
        return resultTableList;
    }

    @Override
    public List<DatabaseTableFiledVo> getTableFiledListByDBInfo(String databaseName, String schemaName, String tableName) {
        List<DatabaseTableFiledVo> resultTableList = new ArrayList<>();
        Object[] params = new Object[2];
        params[0] = schemaName;
        params[1] = tableName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_FILED_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DatabaseTableFiledVo databaseTableFiledVo = new DatabaseTableFiledVo();
            String filedName = (String) stringObjectMap.get("filedname");
            String filedSize = stringObjectMap.get("filedsize").toString();
            String filedType = (String) stringObjectMap.get("filedtype");
            String primaryKey = (String) stringObjectMap.get("primarykey");
            String notNull = (String) stringObjectMap.get("notNull");
            String defaultValue = (String) stringObjectMap.get("defaultvalue");
            String filedComment = (String) stringObjectMap.get("filedcomment");
            databaseTableFiledVo.setFiledName(filedName);
            databaseTableFiledVo.setFiledSize(filedSize);
            databaseTableFiledVo.setFiledType(filedType);
            databaseTableFiledVo.setPrimaryKey("1".equals(primaryKey) ? CommonBoolenEnum.是 : CommonBoolenEnum.否);
            databaseTableFiledVo.setNotNull("1".equals(notNull) ? CommonBoolenEnum.是 : CommonBoolenEnum.否);
            databaseTableFiledVo.setFiledComment(filedComment);
            databaseTableFiledVo.setDefaultValue(defaultValue);
            resultTableList.add(databaseTableFiledVo);
        }
        return resultTableList;
    }

    @Override
    public ExeSqlResult getTableDataListByDBInfo(String tableName, int pageSize, int pageNo) {
        String sql = "select * from " + tableName;
        String sqlPage = this.generatePageSql(sql, pageNo, pageSize);
        BigDecimal count = this.getCountByQuerySql(sql);
        Object[] params = null;
        ExeSqlResult exeSqlResult = this.executeQuery(sqlPage, params);
        exeSqlResult.setResultInteger(count);
        return exeSqlResult;
    }

    @Override
    public BigDecimal getCountByQuerySql(String sql) {
        String sqlCount = "select count(0) count from (" + sql + ")";
        Object[] params = null;
        ExeSqlResult exeSqlResult = this.executeQuery(sqlCount, params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        String count = (String) resultList.get(0).get("count");
        return new BigDecimal(count);
    }

    @Override
    public Boolean runSqlByScriptRunner(String sqlPath) {
        try {
            ScriptRunner runner = new ScriptRunner(conn);
            runner.setEscapeProcessing(false);
            runner.setSendFullScript(true);
            runner.runScript(new InputStreamReader(new FileInputStream(sqlPath), "UTF-8"));
        } catch (Exception e) {
            log.error("执行sql文件异常", e);
            throw new BusinessException("执行sql文件异常");
        }

        return true;
    }

    /**
     * 获取连接
     */
    @Override
    public Boolean getConnection() {
        // 获取连接
        log.info("连接数据库", sessionId, url, username, password);
        conn = JdbcConnectionManager.getConnection(sessionId, url, username, password, driverClassName);
        if (conn == null) {
            return false;
        }
        return true;
    }

    /**
     * 关闭所有资源
     */
    @Override
    public void closeAll() {
        // 关闭结果集对象
        if (rst != null) {
            try {
                rst.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常", e);
            }
        }

        // 关闭PreparedStatement对象
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常", e);
            }
        }

        // 关闭CallableStatement 对象
        if (callableStatement != null) {
            try {
                callableStatement.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常", e);
            }
        }

        // 关闭Connection 对象
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常", e);
            }
        }
    }

    /**
     * insert update delete SQL语句的执行的统一方法
     *
     * @param sql    SQL语句
     * @param params 参数数组，若没有参数则为null
     * @return 受影响的行数
     */
    @Override
    public int executeUpdate(String sql, Object[] params) {
        // 受影响的行数
        int affectedLine = 0;
        try {
            log.info("执行sql: " + sql);
            StringBuffer sb = new StringBuffer();
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    if (params[i] != null) {
                        sb.append(params[i].toString().trim() + ",");
                    }
                }
            }
            String newStr = sb.toString();
            log.info("执行sql 参数: " + newStr);
            // 插入执行sql
            BackupExeSqlLog insertLog = new BackupExeSqlLog();
            insertLog.setUuid(UUID.uuid());
            insertLog.setExeSql(sql);
            insertLog.setExeParams(newStr);
            insertLog.setDatabaseId(autoDataSource.getUuid());
            insertLog.setDatabaseName(autoDataSource.getDatabaseName());
            insertLog.setSchemaName(autoDataSource.getSchemaName());
            insertLog.setExeTime(new Date());
            // 调用SQL
            pst = conn.prepareStatement(sql);

            // 参数赋值
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pst.setObject(i + 1, params[i]);
                }
            }
            /**
             * 在此 PreparedStatement 对象中执行 SQL 语句，
             *  该语句必须是一个 SQL 数据操作语言（Data Manipulation Language，DML）语句，比如 INSERT、UPDATE 或 DELETE
             *  语句；或者是无返回内容的 SQL 语句，比如 DDL 语句。
             */
            affectedLine = pst.executeUpdate();

        } catch (SQLException e) {
            log.error("执行sql异常", e);
        } finally {
            this.closeAll();
        }
        return affectedLine;
    }

    @Override
    public ExeSqlResult executeQuery(String sql, Object[] params) {
        final Boolean connection = this.getConnection();
        if (!connection) {
            throw new BusinessException("获取数据库连接失败:" + url);
        }
        ExeSqlResult exeSqlResult = new ExeSqlResult();
        // 执行SQL获得结果集
        try {
            log.info("执行sql: " + sql);
            StringBuffer sb = new StringBuffer();
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    if (params[i] != null) {
                        sb.append(params[i].toString().trim() + ",");
                    }
                }
            }
            String newStr = sb.toString();
            log.info("执行sql 参数: " + newStr);
            // 插入执行sql
            BackupExeSqlLog insertLog = new BackupExeSqlLog();
            insertLog.setUuid(UUID.uuid());
            insertLog.setExeSql(sql);
            insertLog.setExeParams(newStr);
            insertLog.setDatabaseId(autoDataSource.getUuid());
            insertLog.setDatabaseName(autoDataSource.getDatabaseName());
            insertLog.setSchemaName(autoDataSource.getSchemaName());
            insertLog.setExeTime(new Date());
            autoExeSqlLogMapper.insert(insertLog);
            // 调用SQL
            pst = conn.prepareStatement(sql);

            // 参数赋值
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pst.setObject(i + 1, params[i]);
                }
            }

            // 执行
            rst = pst.executeQuery();
            // 创建ResultSetMetaData对象
            ResultSetMetaData rsmd = null;

            // 结果集列数
            int columnCount = 0;
            rsmd = rst.getMetaData();

            // 获得结果集列数
            columnCount = rsmd.getColumnCount();

            // 创建List
            List<Map<String, Object>> list = new ArrayList<>();
            List<String> filedList = new ArrayList<>();

            for (int i = 1; i <= columnCount; i++) {
                filedList.add(rsmd.getColumnLabel(i));
            }
            // 将ResultSet的结果保存到List中
            while (rst.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(rsmd.getColumnLabel(i), rst.getObject(i));
                }
                //每一个map代表一条记录，把所有记录存在list中
                list.add(map);
            }
            exeSqlResult.setResultList(list);
            exeSqlResult.setFiledList(filedList);
        } catch (SQLException e) {
            log.error("执行sql异常", e);
            throw new BusinessException("执行sql异常");
        } finally {
            this.closeAll();
        }
        exeSqlResult.setExecSql(sql);
        exeSqlResult.setExecSqlParams(params);
        exeSqlResult.setSessionId(sessionId);
        return exeSqlResult;
    }

    @Override
    public void trueAutoCommit() {
        try {
            this.conn.setAutoCommit(true);
        } catch (SQLException e) {
            log.error("关闭事物失败", e);
            throw new BusinessException("关闭事物失败");
        }
    }

    @Override
    public void falseAutoCommit() {
        try {
            this.conn.setAutoCommit(false);
        } catch (SQLException e) {
            log.error("开启事物失败", e);
            throw new BusinessException("开启事物失败");
        }
    }

    @Override
    public void commitTransactional() {
        try {
            this.conn.commit();
        } catch (SQLException e) {
            log.error("提交事物失败", e);
            throw new BusinessException("提交事物失败");
        }
    }

    @Override
    public Boolean dropDatabase(String name) {
        String updateSql = "DROP DATABASE IF EXISTS " + name;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean renameDatabase(String name, String toName) {
        String updateSql = "UPDATE pg_database" +
                "        SET datname = '" + toName + "'" +
                "        WHERE datname = '" + name + "'";
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean dropDataSchema(String name) {
        String updateSql = "drop schema " + name + " cascade ";
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean renameDataSchema(String name, String toName) {
        String updateSql = "ALTER SCHEMA  " + name + " RENAME TO " + toName;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean dropDataTable(String name) {
        String updateSql = "DROP TABLE " + name;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean renameDataTable(String name, String toName) {
        String updateSql = "alter table " + name + " rename to " + toName;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean addDataTableFiled(String tableName, DatabaseTableFiledVo addDatabaseTableFiledVo) {
        String filed = addDatabaseTableFiledVo.getFiledName() + " " + addDatabaseTableFiledVo.getFiledType() + "(" + addDatabaseTableFiledVo.getFiledSize() + ")";
        String updateSql = "ALTER TABLE " + tableName + " ADD " + filed;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean deleteDataTableFiled(String tableName, String tableFiled) {
        String updateSql = "alter table " + tableName + " drop column if exists " + tableFiled;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean alertDataTableFiled(String tableName, String tableFiled, DatabaseTableFiledVo toDatabaseTableFiledVo) {
        String type = toDatabaseTableFiledVo.getFiledType() + "(" + toDatabaseTableFiledVo.getFiledSize() + ")";

        String updateNameSql = "alter table " + tableName + " RENAME " + tableFiled + " to " + toDatabaseTableFiledVo.getFiledName();
        String updateSql = "alter table " + tableName + " alter COLUMN  " + toDatabaseTableFiledVo.getFiledName() + " type " + type;
        Object[] params = null;
        int i = this.executeUpdate(updateNameSql, params);
        i += this.executeUpdate(updateSql, params);
        return i > 0 ? true : false;
    }

    @Override
    public Boolean addFiledPk(String tableName, String tableFiled) {
        String pkUpdate = "ALTER TABLE \"" + tableName + "\" ADD CONSTRAINT \"" + tableFiled + "_pkey\" PRIMARY KEY (\"" + tableFiled + "\");";
        Object[] params = null;
        int i = this.executeUpdate(pkUpdate, params);
        return i > 0 ? true : false;
    }

    @Override
    public Boolean deleteFiledPk(String tableName, String tableFiled) {
        String pkUpdate = "ALTER TABLE \"" + tableName + "\" DROP CONSTRAINT \"" + tableFiled + "_pkey\"";
        Object[] params = null;
        int i = this.executeUpdate(pkUpdate, params);
        return i > 0 ? true : false;
    }

}
